"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证prepare execute
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、插入数据
    4、执行无占位符语句
    5、执行有占位符语句
    6、执行limit后的占位符语句
    7、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、插入成功
    4、执行成功
    5、执行成功
    6、执行成功
    7、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_38"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_tb = f"drop table if exists tab_protocol_0038; " \
                    f"create table tab_protocol_0038(a int,b text," \
                    f"c bool,d numeric,e binary(10));"
        self.insert = f"insert tab_protocol_0038 values(11,'dhs',false,56.222,'aaa'::bytea); "
        self.prepare1 = f"prepare pre_protocol_0038_01 as 'select * from tab_protocol_0038'; " \
                        f"execute pre_protocol_0038_01;"
        self.prepare2 = f"prepare pre_protocol_0038_02(int) as " \
                        f"'select * from tab_protocol_0038 where a=?';" \
                        f"execute pre_protocol_0038_02(11); "
        self.prepare3 = f"prepare pre_protocol_0038_03(int) as " \
                        f"'select * from tab_protocol_0038 limit ?'; " \
                        f"execute pre_protocol_0038_03(11);"
        self.clean_tb = f"drop table if exists tab_protocol_0038 cascade constraints; " \
                        f"deallocate pre_protocol_0038_01;" \
                        f"deallocate pre_protocol_0038_02;" \
                        f"deallocate pre_protocol_0038_03;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tb, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库插入数据;expect:创建成功--'
        logger.info(text)
        insert_res = my_sh.execut_db_sql(sql=self.insert, dbname=self.db_name, host=self.pri_node.db_host,
                                         port=my_port, user=self.user, password=self.password)
        logger.info(insert_res)
        self.assertNotIn('ERROR', insert_res, '执行失败' + text)

        text = '--step4:M*连接数据库执行无占位符语句;expect:创建成功--'
        logger.info(text)
        pre_res = my_sh.execut_db_sql(sql=self.prepare1, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pre_res)
        self.assertNotIn('ERROR', pre_res, '执行失败' + text)
        self.assertIn(f'11\tdhs\t0\t56\taaa', pre_res,
                      '执行失败' + text)

        text = '--step5:M*连接数据库执行有占位符语句;expect:成功--'
        logger.info(text)
        pre_res = my_sh.execut_db_sql(sql=self.prepare2, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pre_res)
        self.assertNotIn('ERROR', pre_res, '执行失败' + text)
        self.assertIn(f'11\tdhs\t0\t56\taaa', pre_res,
                      '执行失败' + text)

        text = '--step6:M*连接数据库执行limit后占位符语句;expect:成功--'
        logger.info(text)
        pre_res = my_sh.execut_db_sql(sql=self.prepare3, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pre_res)
        self.assertNotIn('ERROR', pre_res, '执行失败' + text)
        self.assertIn(f'11\tdhs\t0\t56\taaa', pre_res,
                      '执行失败' + text)

    def tearDown(self):
        text = '--step7:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
